from datetime import datetime
from typing import Any, Dict, Optional, List

from fastapi import APIRouter, Depends, Query, Body
from sqlalchemy import text, bindparam
from sqlalchemy.ext.asyncio import AsyncSession

from app.db.session import get_db
from app.schemas.task import taskRequest, updateTaskRequest
from app.utils.token import get_current_user

router = APIRouter()


@router.get('/', summary="查询任务列表")
async def get_tasks(
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
    page: int = Query(1, ge=1, description="页码"),
    page_size: int = Query(10, ge=1, le=100, description="每页条数"),
    keyword: Optional[str] = Query(None, description="关键词搜索"),
    sort_by: Optional[str] = Query(
        None, description="排序方式，可选值：priority_asc, priority_desc"
    ),
    status: Optional[str] = Query(None, description="是否归档"),
) -> Dict[str, Any]:
    sub = user_info.get("sub")
    user_id = sub.get("id", None)  # type: ignore
    role = sub.get("role", None)  # type: ignore
    if not user_id:
        return {"code": -1, "error": "无效用户"}

    # 分页
    offset = (page - 1) * page_size
    params: Dict[str, Any] = {}

    # 是否删除
    conditions = "tp.is_deleted = 0"

    # 是否归档
    if status == "archived":
        conditions += " AND tp.status = 'archived'"
    else:
        conditions += " AND tp.status != 'archived'"

    # 关键词筛选
    if keyword:
        conditions += " AND (tp.title LIKE :keyword OR CAST(tp.id AS CHAR) LIKE :keyword)"
        params["keyword"] = f"%{keyword}%"

    # 用户权限限制
    if role != "super_admin":
        conditions += " AND tp.user_id = :user_id"
        params["user_id"] = user_id

    # 排序逻辑
    order_clause = "tp.id ASC"
    if sort_by == "priority_asc":
        order_clause = """
            CASE tp.priority
                WHEN 'low' THEN 1
                WHEN 'medium' THEN 2
                WHEN 'high' THEN 3
                ELSE 4
            END ASC,
            tp.id ASC
        """
    elif sort_by == "priority_desc":
        order_clause = """
            CASE tp.priority
                WHEN 'high' THEN 1
                WHEN 'medium' THEN 2
                WHEN 'low' THEN 3
                ELSE 4
            END ASC,
            tp.id ASC
        """

    # 查询总数
    count_sql = text(f"SELECT COUNT(*) FROM track_plan tp WHERE {conditions}")
    total_result = await db.execute(count_sql, params)
    total = total_result.scalar()

    # 查询任务列表
    task_sql = text(f"""
        SELECT tp.id, tp.parent_id, tp.title, tp.description, tp.status, tp.priority, tp.is_deleted, tp.updated_at,
            CASE
                WHEN p.nickname IS NULL OR TRIM(p.nickname) = '' THEN u.email
                ELSE p.nickname
            END AS username
        FROM track_plan tp
        LEFT JOIN profile p ON tp.user_id = p.user_id
        LEFT JOIN user u ON tp.user_id = u.id
        WHERE {conditions}
        ORDER BY {order_clause}
        LIMIT :limit OFFSET :offset
    """)

    params.update({"limit": page_size, "offset": offset})
    result = await db.execute(task_sql, params)
    rows = result.fetchall()
    tasks = [dict(row._mapping) for row in rows]  # type: ignore

    return {
        "code": 0,
        "data": {
            "total": total,
            "tasks": tasks
        }
    }


async def get_sub_tasks_recursive(parent_id: int, db: AsyncSession):
    """递归查询所有子任务，保留 user_id 等完整字段"""
    sql = text("""
        SELECT id, user_id, title, description, status, priority, parent_id, is_deleted, updated_at
        FROM track_plan
        WHERE parent_id = :parent_id AND is_deleted = 0
    """)
    result = await db.execute(sql, {"parent_id": parent_id})
    rows = result.fetchall()

    tasks: Any = []
    for row in rows: 
        task = dict(row._mapping)  # type: ignore
        task["sub_tasks"] = await get_sub_tasks_recursive(task["id"], db)
        tasks.append(task)  # type: ignore

    return tasks  # type: ignore


@router.get('/{id}', summary="查询单个任务详情")
async def get_task_detail(
    id: int,
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    user_id = sub.get('id', None)  # type: ignore
    role = sub.get('role', None)  # type: ignore
    if not user_id:
        return {"code": -1, "error": "无效用户"}

    sql = text("""
        SELECT id, user_id, title, description, status, priority, parent_id, is_deleted, updated_at
        FROM track_plan
        WHERE id = :id AND is_deleted = 0
    """)
    result = await db.execute(sql, {"id": id})
    row = result.fetchone()
    if not row:
        return {"code": -1, "error": "任务不存在或已被删除"}

    task = dict(row._mapping)  # type: ignore

    if role != 'super_admin' and task.get('user_id') != user_id:
        return {"code": -1, "error": "无权限查看该任务"}

    task["sub_tasks"] = await get_sub_tasks_recursive(task["id"], db)

    return {"code": 0, "data": task}


@router.post('/', summary="发布任务")
async def create_task(
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
    data: taskRequest = Body(...)
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    user_id = sub.get('id', None)  # type: ignore
    if not user_id:
        return {"code": -1, "error": "无效用户"}

    title = data.title
    description = data.description
    priority = data.priority
    parent_id = data.parent_id

    now = datetime.now()
    try:
        sql = text("""
            INSERT INTO track_plan (user_id, parent_id, title, description, status, priority, is_deleted, created_at, updated_at)
            VALUES (:user_id, :parent_id, :title, :description, 'pending', :priority, 0, :created_at, :updated_at)
        """)
        await db.execute(sql, {
            "user_id": user_id,
            "parent_id": parent_id,
            "title": title,
            "description": description,
            "priority": priority,
            "created_at": now,
            "updated_at": now
        })
        await db.commit()
    except Exception as e:
        await db.rollback()
        print(str(e))
        return {"code": -1, "error": "任务发布失败"}

    return {"code": 0, "message": "任务发布成功"}


@router.put('/{id}', summary="修改任务")
async def update_task(
    id: int,
    data: updateTaskRequest = Body(...),
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    user_id = sub.get('id', None)  # type: ignore
    role = sub.get('role', None)  # type: ignore
    if not user_id:
        return {"code": -1, "error": "无效用户"}

    # 检查任务是否存在，获取 user_id
    check_sql = text(
        "SELECT user_id FROM track_plan WHERE id = :id AND is_deleted = 0")
    result = await db.execute(check_sql, {"id": id})
    row = result.fetchone()
    if not row:
        return {"code": -1, "error": "任务不存在或已被删除"}

    task_owner_id = row[0]
    print(task_owner_id, user_id)
    if role != 'super_admin' and task_owner_id != user_id:
        return {"code": -1, "error": "无权限修改该任务"}

    now = datetime.now()
    try:
        # 1. 更新主任务
        update_sql = text("""
            UPDATE track_plan
            SET title = :title,
                description = :description,
                priority = :priority,
                status = :status,
                updated_at = :updated_at
            WHERE id = :id AND is_deleted = 0
        """)
        await db.execute(update_sql, {
            "title": data.title,
            "description": data.description,
            "priority": data.priority,
            "status": data.status,
            "updated_at": now,
            "id": id
        })

        # 2. 状态级联：如果是 archived 或 completed，同时更新子任务状态
        if data.status in ["archived", "completed"]:
            cascade_sql = text("""
                UPDATE track_plan
                SET status = :status,
                    updated_at = :updated_at
                WHERE parent_id = :parent_id AND is_deleted = 0
            """)
            await db.execute(cascade_sql, {
                "status": data.status,
                "updated_at": now,
                "parent_id": id
            })

        await db.commit()
    except Exception as e:
        await db.rollback()
        print(str(e))
        return {"code": -1, "error": "修改任务失败"}

    return {"code": 0, "message": "任务修改成功"}


async def get_all_sub_task_ids(parent_id: int, db: AsyncSession) -> List[int]:
    sql = text(
        "SELECT id FROM track_plan WHERE parent_id = :parent_id AND is_deleted = 0")
    result = await db.execute(sql, {"parent_id": parent_id})
    rows = result.fetchall()
    ids = []
    for row in rows:
        sub_id = row[0]
        ids.append(sub_id)  # type: ignore
        sub_ids = await get_all_sub_task_ids(sub_id, db)  # type: ignore
        ids.extend(sub_ids)  # type: ignore
    return ids  # type: ignore


@router.delete('/{id}', summary="删除任务（软删除）")
async def delete_task(
    id: int,
    db: AsyncSession = Depends(get_db),
    user_info: Dict[str, Any] = Depends(get_current_user),
) -> Dict[str, Any]:
    sub = user_info.get('sub')
    user_id = sub.get('id', None)  # type: ignore
    role = sub.get('role', None)  # type: ignore
    if not user_id:
        return {"code": -1, "error": "无效用户"}

    check_sql = text(
        "SELECT user_id FROM track_plan WHERE id = :id AND is_deleted = 0")
    result = await db.execute(check_sql, {"id": id})
    row = result.fetchone()
    if not row:
        return {"code": -1, "error": "任务不存在或已被删除"}

    task_owner_id = row[0]
    if role != 'super_admin' and task_owner_id != user_id:
        return {"code": -1, "error": "无权限删除该任务"}

    now = datetime.now()
    sub_task_ids = await get_all_sub_task_ids(id, db)  # type: ignore
    all_ids_to_delete = [id] + sub_task_ids  # type: ignore

    try:
        delete_sql = text(
            "UPDATE track_plan SET is_deleted = 1, updated_at = :updated_at WHERE id IN :ids"
        ).bindparams(bindparam("ids", expanding=True))  # type: ignore
        await db.execute(delete_sql, {"updated_at": now, "ids": all_ids_to_delete})
        await db.commit()
    except Exception as e:
        await db.rollback()
        print(str(e))
        return {"code": -1, "error": "删除任务失败"}

    return {"code": 0, "message": "任务已删除"}
